from flask import Flask, url_for, request, redirect, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 设置数据库连接地址
DB_URI = 'mysql+pymysql://manliremote:manlimysql0.@114.67.102.187:3306/test2'
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
# 是否追踪数据库修改，一般不开启, 会影响性能
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 是否显示底层执行的SQL语句
app.config['SQLALCHEMY_ECHO'] = True

# 初始化db,关联flask 项目
db = SQLAlchemy(app)


# 创建模型
class Students(db.Model):
    __tablename__ = 'students'  # 数据库表名

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    fullname = db.Column(db.String(30))
    nickname = db.Column(db.String(30))

    def __repr__(self):
        return "<Students(name='%s', fullname='%s', nickname='%s')>" % (
            self.name, self.fullname, self.nickname)


# （单条）添加数据
@app.route('/add', methods=["GET"])
def add():
    # 实例化 Students 模型对象
    user = Students(name='yy', fullname='yoyo')
    # 添加到会话，并用commit提交数据
    db.session.add(user)
    db.session.commit()
    return {
        "code": 0,
        "msg": "create success!"
    }


# 一次添加多个数据，用db.session.add_all()
@app.route('/addAll', methods=["GET"])
def addAll():
    # 实例化 Students 模型对象
    user1 = Students(name='yy1', fullname='yoyo1')
    user2 = Students(name='yy2', fullname='yoyo2')
    user3 = Students(name='yy3', fullname='yoyo3')
    # 添加到会话，并用commit提交数据
    # db.session.add(user)
    db.session.add_all([user1, user2, user3])
    db.session.commit()
    return {
        "code": 0,
        "msg": "create success!"
    }


@app.route('/queryAll', methods=["GET"])
def queryAll():
    # 返回列表
    all = Students.query.all()
    print(all)
    return {
        "code": 0,
        "msg": "success"
    }


# 修改
# Students.query.filter_by(name='yy').update({"fullname": "xx"})
# db.session.commit()

# 删除
# Students.query.filter_by(name='yy').delete()
# db.session.commit()

# 不需要commit 自动保存, 默认False
# app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

if __name__ == '__main__':
    # 创建表
    db.create_all()
    app.run(debug=True)
